package pos.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pos.domain.Equipo;
import pos.domain.Usuario;

public class JDBCUsuarioDAO implements IUsuarioDAO{

	private Connection conn;
	
	public JDBCUsuarioDAO()
	{
		conn = ConnectionManager.getInstance().checkOut();
	}
	public JDBCUsuarioDAO(Connection conn)
	{
		this.conn = conn;
	}
	
	
	public Usuario selectUsuario( String userName){
		PreparedStatement stmt = null;
		ResultSet result = null;
		Usuario u = null;
		String sql = "SELECT * FROM usuarios WHERE (userName = ?)";
		
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, userName);
			result = stmt.executeQuery();
			
			result.next();
			u = new Usuario();
			u.setUserName(result.getString("userName"));
			u.setPassword(result.getString("password"));
			u.setEmail(result.getString("email"));
			u.setFingreso(result.getDate("fingreso"));
		} catch (SQLException e){
			System.out.println ("Message" + e.getMessage());
			System.out.println ("SQLState: " + e.getSQLState());
			System.out.println ("ErrorCode: " + e.getErrorCode());
		}finally {
			try{
				if (result != null){
					result.close();
				}
				if (stmt != null){
					stmt.close();
				}
			}catch (SQLException e){
			}
		}
		return u;
	}
	
	public Usuario selectUsuarioByOID( String usuarioOID){
		PreparedStatement stmt = null;
		ResultSet result = null;
		Usuario u = null;
		String sql = "SELECT * FROM usuarios WHERE (OID = ?)";
		
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, usuarioOID);
			result = stmt.executeQuery();
			
			result.next();
			u = new Usuario();
			u.setUserName(result.getString("userName"));
			u.setPassword(result.getString("password"));
			u.setEmail(result.getString("email"));
			u.setFingreso(result.getDate("fingreso"));
		} catch (SQLException e){
			System.out.println ("Message" + e.getMessage());
			System.out.println ("SQLState: " + e.getSQLState());
			System.out.println ("ErrorCode: " + e.getErrorCode());
		}finally {
			try{
				if (result != null){
					result.close();
				}
				if (stmt != null){
					stmt.close();
				}
			}catch (SQLException e){
			}
		}
		return u;
	}
	public String selectUsuarioOID( String userName)
	{
		    PreparedStatement stmt = null;
	        ResultSet result = null;
	        String usuariosOID = null;
	        String sql = "SELECT * FROM usuarios WHERE (userName = ? ) ";

	        try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1, userName);

	            result = stmt.executeQuery();

	            result.next();
	            usuariosOID = result.getString("OID");
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (result != null) {
	                    result.close();
	                }

	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }

	        return usuariosOID;
		
	}
	
	
	
	public List<Usuario> selectListaInscritoUsuario( String idEvento){
		List l = new ArrayList();
		String sql = "SELECT * FROM usuarios U, inscripciones I, eventos E WHERE I.usuarioOID = U.usuarioOID AND I.eventoOID = E.eventoOID AND (E.eventoOID = ?)";
		PreparedStatement stmt = null;
		ResultSet result = null;
		
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idEvento);
			result = stmt.executeQuery();
			
			while (result.next()){
				Usuario u = selectUsuario(result.getString("userName"));
				l.add(u);
			}
		}catch (SQLException e){
			System.out.println ("Message" + e.getMessage());
			System.out.println ("SQLState: " + e.getSQLState());
			System.out.println ("ErrorCode: " + e.getErrorCode());
		}finally {
			try{
				if (result != null){
					result.close();
				}
				if (stmt != null){
					stmt.close();
				}
			}catch (SQLException e){
			}
		}
		return l;
	}
	
	public List<Usuario> selectListaPeticionesUsuario( String idEvento){
		List l = new ArrayList();
		String sql = "SELECT * FROM usuarios U, invitados I, eventos E WHERE I.usuarioOID = U.usuarioOID AND I.eventoOID = E.eventoOID AND (E.eventoOID = ?)";
		PreparedStatement stmt = null;
		ResultSet result = null;
		
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idEvento);
			result = stmt.executeQuery();
			
			while (result.next()){
				Usuario u = selectUsuario(result.getString("userName"));
				l.add(u);
			}
		}catch (SQLException e){
			System.out.println ("Message" + e.getMessage());
			System.out.println ("SQLState: " + e.getSQLState());
			System.out.println ("ErrorCode: " + e.getErrorCode());
		}finally {
			try{
				if (result != null){
					result.close();
				}
				if (stmt != null){
					stmt.close();
				}
			}catch (SQLException e){
			}
		}
		return l;
	}
	
	public Usuario selectDeportistaCreador( String idEvento){
		Usuario u = new Usuario();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "SELECT * FROM deportistas D, eventoActividadGrupo EAG, evento E, usuario U WHERE (E.OID = ?) AND " +
				"E.OID = EAG.IOD AND EAG.deportistaOID = D.OID AND D.OID = U.OID";
		
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idEvento);
			result = stmt.executeQuery();
			u.setUserName(result.getString("userName"));
			u.setPassword(result.getString("password"));
			u.setEmail(result.getString("email"));
			u.setFingreso(result.getDate("fingreso"));
		} catch (SQLException e){
			System.out.println ("Message" + e.getMessage());
			System.out.println ("SQLState: " + e.getSQLState());
			System.out.println ("ErrorCode: " + e.getErrorCode());
		}finally {
			try{
				if (result != null){
					result.close();
				}
				if (stmt != null){
					stmt.close();
				}
			}catch (SQLException e){
			}
		}
		return u;
	}
	
	public Usuario selectEquipoCreador( String idEvento){
		Usuario u = new Usuario();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "SELECT * FROM eventos E, eventoBuscaEquipo EBE, eventoBuscaComponentes EBC, " +
				"equipo EQ, usuarios U WHERE (E.OID = ?) AND E.OID = EBE.OID OR E.OID = EBC.OID AND " +
				"EBE.equipoOID = EQ.OID OR EBC.equipoOID = EQ.OID AND EQ.OID = U.OID";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, idEvento);
			result = stmt.executeQuery();
			u.setUserName(result.getString("userName"));
			u.setPassword(result.getString("password"));
			u.setEmail(result.getString("email"));
			u.setFingreso(result.getDate("fingreso"));
		} catch (SQLException e){
			System.out.println ("Message" + e.getMessage());
			System.out.println ("SQLState: " + e.getSQLState());
			System.out.println ("ErrorCode: " + e.getErrorCode());
		}finally {
			try{
				if (result != null){
					result.close();
				}
				if (stmt != null){
					stmt.close();
				}
			}catch (SQLException e){
			}
		}
		return u;
	}
	@Override
	public void insertUsuario(Equipo equipo) {
		// TODO Auto-generated method stub
		
	}

}